set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'mov_addToQueue' AND type = 'P') DROP PROCEDURE mov_addToQueue
GO




-- =============================================
-- Modified:    Sourov Chatterjee
-- Create date: 6/3/2011
-- Description:	This procedure add movies to queue

-- =============================================
CREATE PROCEDURE [dbo].[mov_addToQueue] 
--@cus_id int =1,
@cus_email varchar(50) = 'cbarker.yahoo.com',
@movie_id int =1
AS
BEGIN

--SET IDENTITY_INSERT cus_queue ON

    declare @q_order int 
    declare @cus_id int
    declare @queued int
    declare @rented int

    Declare @quenumber int;
	Set @quenumber = (Select ISNULL(max(que_ID),0) from dbo.cus_queue) + 1
	SELECT @quenumber
    
    SET @cus_id = (Select cus_id from Customer where cus_email= @cus_email )
    Set @q_order = (select ISNULL(max(que_order),0) + 1 from cus_queue where  cus_id =  @cus_id)
    
   -- Prevent dup addition if rented
    SET @rented = (Select count(*) from rental r JOIN cus_queue cq ON r.que_id = cq.que_id
	              where cq.cus_id = @cus_id and rent_receive_date is null and cq.movie_id = @movie_id )

   -- Prevent dup addition if queued
     SET @queued = (SELECT count(*) from cus_queue where cus_id = @cus_id and movie_id = @movie_id)

    IF @rented < 1 AND 	@queued  < 1  
	INSERT INTO dbo.cus_queue
	VaLUES(@quenumber,@q_order, @cus_id ,@movie_id)
   
END

